﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Http;
using Mono.TextTemplating;
using Newtonsoft.Json;
using WHLRDF.Application.BLL;
using WHLRDF.Application.Model;
using WHLRDF.Code.Model;
using WHLRDF.ORM;

namespace WHLRDF.Code.BLL
{
    /// <summary>
    /// Data 相关操作类
    /// </summary>
    public partial class DbCodeService
    {
        /// <summary>
        /// 获取表名
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="IsTable"></param>
        /// <returns></returns>
        public virtual List<DbTableEntity> GetDbTable(string id, bool IsTable)
        {
            DbServerEntity entity = this.GetById<DbServerEntity>(id);
            List<DbTableEntity> lstEntity = new List<DbTableEntity>();
            if (entity == null)
            {
                return lstEntity;
            }
            DbServerEntity dbServer = this.GetById<DbServerEntity>(entity.DbServerId.ToString());
            if (dbServer == null)
            {
                return lstEntity;
            }
            var dbHelper = this.CreateRepository(dbServer.ProviderType, dbServer.ConntionString);
            //switch ((ProviderType)entity.ProviderType)
            //{
            //    case ProviderType.MsSql:
            //        db = new DbSQLServer((ProviderType)entity.ProviderType, entity.ConntionString);
            //        break;
            //    case ProviderType.MySQL:
            //        db = new DbMySQL((ProviderType)entity.ProviderType, entity.ConntionString);
            //        break;
            //}
            List<Dictionary<string, object>> lstTable = IsTable ? dbHelper.GetTable(entity.DbServerName) : dbHelper.GetView(entity.DbServerName);
            ICriterion criter = Expression.And(
                              Expression.Eq(DbTableEntity.__DbServerId, entity.DbServerId),
                           Expression.Eq(DbTableEntity.__IsDeleted, false));
            lstEntity = this.Query<DbTableEntity>(criter, Order.Asc(DbTableEntity.__DbTableName)).ToList();
            if (lstTable != null && lstTable.Count > 0)
            {
                foreach (Dictionary<string, object> table in lstTable)
                {
                    var tablearr = table.ToArray();
                    var exist = lstEntity.Where(x => x.DbTableName.ToLower().Equals(tablearr[1].Value.ToStr().ToLower())).FirstOrDefault();
                    if (exist != null)
                    {
                        exist.ProviderType = entity.ProviderType;
                        //  lstEntity.Add(exist);
                        continue;
                    }
                    var table1 = new DbTableEntity()
                    {
                        DbServerId = entity.DbServerId,
                        DbTableId = MyGenerateHelper.GenerateOrder(),
                        LocalName = tablearr[1].Value.ToString(),
                        DbTableName = tablearr[1].Value.ToString(),
                        IsTable = tablearr[4].Value.ToBool(),
                        State = EntityState.Add,
                        IsEnableCreate = true,
                        IsEnableIsDeleted = true,
                        IsEnableLastModify = true,
                        ProviderType = entity.ProviderType
                    };
                    lstEntity.Add(table1);
                    this.SaveOrUpdate<DbTableEntity>(table1);
                }


                //lstEntity.ForEach(x => {
                //    x.DbTableName = (x.DbTableName != x.LocalName) ? x.DbTableName + "(" + x.LocalName + ")" : x.DbTableName;
                //});

            }
            if (lstEntity != null && lstEntity.Count > 0)
            {
                lstEntity = lstEntity.Where(x => x.IsTable == IsTable).ToList();
            }
            return lstEntity;
        }

        /// <summary>
        /// 获取所有字段
        /// </summary>
        /// <param name="tableid"></param>
        /// <returns></returns>
        public virtual List<DbColumnEntity> GetColumns(string tableid)
        {
            int providerType = ProviderType.SqlServer.GetHashCode();
            DbTableEntity entity = new DbTableEntity();
            return GetColumns(tableid, ref entity, ref providerType);
        }
        /// <summary>
        /// 获取字段
        /// </summary>
        /// <param name="tableid"></param>
        /// <param name="entity"></param>
        /// <param name="providerType">数据库类型</param>
        /// <returns></returns>
        public virtual List<DbColumnEntity> GetColumns(string tableid, ref DbTableEntity entity, ref int providerType)
        {
            return GetColumns(tableid, ref entity, ref providerType, true);
        }
        /// <summary>
        /// 获取字段
        /// </summary>
        /// <param name="tableid"></param>
        /// <param name="entity"></param>
        /// <param name="providerType">数据库类型</param>
        /// <param name="isSyncColumn">同步字段</param>
        /// <returns></returns>
        public virtual List<DbColumnEntity> GetColumns(string tableid, ref DbTableEntity entity, ref int providerType, bool isSyncColumn = true)
        {
            if ((entity == null || string.IsNullOrWhiteSpace(entity.DbTableId)))
            {
                entity = this.GetById<DbTableEntity>(tableid);
            }
            if (entity == null)
            {
                return null;
            }

            DbServerEntity dbServer = this.GetById<DbServerEntity>(entity.DbServerId);
            if (dbServer == null)
            {
                return null;
            }

            ICriterion criter = Expression.And(
                               Expression.Eq("DbTableId", tableid),
                              Expression.Eq("IsDeleted", false));

            List<DbColumnEntity> lstEntity = new List<DbColumnEntity>();
            lstEntity = this.Query<DbColumnEntity>(criter, Order.Asc(DbColumnEntity.__OrderNo)).ToList();
            providerType = entity.ProviderType = dbServer.ProviderType;
            if (!isSyncColumn)
            {
                return lstEntity;
            }
            var dbHelper = this.CreateRepository(dbServer.ProviderType, dbServer.ConntionString);
            List<Dictionary<string, object>> dbColumns = dbHelper.GetDbColumn(entity.DbTableName, dbServer.DbServerName, entity.IsTable);
            var lstcodeType = this.Query<SysFieldTypeEntity>(Expression.Eq(SysFieldTypeEntity.__IsDeleted, false));
            var lstDbType = AppHttpContext.GetSerivce<IDbFieldRelationTypeService>().GetAll((ProviderType)dbServer.ProviderType);
            if (dbColumns != null && dbColumns.Count > 0)
            {
                int index = 0;

                foreach (Dictionary<string, object> item in dbColumns)
                {
                    index++;
                    var column = item.ToArray();
                    var exist = lstEntity.Where(x => x.ColumnName.ToLower().Equals(column[1].Value.ToString().ToLower())).FirstOrDefault();
                    if (exist != null)
                    {
                        //  lstEntity.Add(exist);
                        continue;
                    }

                    int DbTypeId = 1;
                    int sysDbTypeid = 2;
                    var dbType = lstDbType.Where(x => x.DbTypeName == column[11].Value.ToStr()).FirstOrDefault();
                    if (dbType != null)
                    {
                        DbTypeId = dbType.DbFieldRelationTypeId;
                        sysDbTypeid = dbType.SysFieldTypeId;
                    }
                    SysFieldTypeEntity sysDbType = lstcodeType.Where(x => x.SysFieldTypeId == sysDbTypeid).FirstOrDefault();
                    if (sysDbType == null)
                    {
                        sysDbType = new SysFieldTypeEntity
                        {
                            ControlType = 1,
                            VaildKey = "",
                            SysFieldTypeId = 2
                        };
                    }
                    var dbCol = new DbColumnEntity()
                    {
                        ColumnId = MyGenerateHelper.GenerateOrder(),
                        DbTableId = entity.DbTableId,
                        OrderNo = column[0].Value.ToInt(),
                        Format = "",
                        ColumnName = column[1].Value.ToStr().Trim(),
                        ColumnCaption = column[10].Value.ToStr().Trim(),
                        ColumnKeyName = "lbl_" + entity.DbTableName + "_" + column[1].Value.ToString(),
                        IsIdentifier = column[2].Value.ToBool(),
                        IsPrimary = column[3].Value.ToBool(),
                        MaxLength = column[6].Value.ToInt(),
                        AllowDBNull = column[8].Value.ToBool(),
                        DefaultValue = column[9].Value.ToStr().Replace("(", "").Replace(")", ""),
                        Remark = column[10].Value.ToStr(),
                        DbFieldRelationTypeId = DbTypeId,
                        DbTypeName = column[11].Value.ToStr(),
                        IsGridVisible = true,
                        ControlType = sysDbType.ControlType,
                        IsEnabled = true,
                        IsEditVisible = !column[3].Value.ToBool(),
                        IsSort = true,
                        AllowInsert = true,
                        AllowUpdate = true,
                        IsFilter = (!column[3].Value.ToBool() && (sysDbType.ControlType == 1)),
                        IsFrozen = false,
                        State = EntityState.Add,
                        GridWidth = 150,
                        LabelWidth = 100,
                        InputWidth = 200,
                        VaildKey = sysDbType.VaildKey,
                        RegValidate = "",
                        SysFieldTypeId = sysDbTypeid
                    };
                    switch (dbCol.ColumnName.ToLower())
                    {
                        case "createby":
                            dbCol.ColumnCaption = "创建人";
                            dbCol.AllowUpdate = false;
                            dbCol.IsGridVisible = false;
                            dbCol.IsEditVisible = false;
                            dbCol.IsSort = false;
                            break;
                        case "createdate":
                            dbCol.ColumnCaption = "创建时间";
                            dbCol.AllowUpdate = false;
                            dbCol.IsGridVisible = false;
                            dbCol.IsEditVisible = false;
                            dbCol.IsSort = false;
                            break;
                        case "lastmodifyuserid":
                            dbCol.ColumnCaption = "修改人";
                            dbCol.IsGridVisible = false;
                            dbCol.IsEditVisible = false;
                            dbCol.IsSort = false;
                            break;
                        case "lastmodifydate":
                            dbCol.IsGridVisible = true;
                            dbCol.IsEditVisible = false;
                            dbCol.IsSort = true;
                            dbCol.IsDefaultSort = true;
                            dbCol.ColumnCaption = "修改时间";
                            break;
                        case "isdeleted":
                            dbCol.IsGridVisible = false;
                            dbCol.IsEditVisible = false;
                            dbCol.ColumnCaption = "是否删除";
                            break;
                        case "remark":
                        case "comments":
                            dbCol.ColumnCaption = "描述";
                            dbCol.IsGridVisible = false;
                            break;
                    }
                    lstEntity.Add(dbCol);
                    this.SaveOrUpdate<DbColumnEntity>(dbCol);
                }
                //if (lstEntity != null && lstEntity.Count > 0)
                //{
                //    SessionFactory.BatchSave<DbColumnsEntity>(lstEntity);
                //}



            }

            return lstEntity;
        }

        private int GetDbTypeId(string dbTypeName, int ProvderType)
        {
            Dictionary<string, DbFieldRelationTypeEntity> lstDbType = new Dictionary<string, DbFieldRelationTypeEntity>();

            if (!lstDbType.ContainsKey(dbTypeName.ToLower())
                || lstDbType[dbTypeName.ToLower()] == null)
            {

                ICriterion criter = Expression.And(Expression.Eq("IsDeleted", false),
                   Expression.And(Expression.Eq("DbProviderId", ProvderType),
                   Expression.Eq("DbTypeName", dbTypeName)));
                DbFieldRelationTypeEntity entity = this.Query<DbFieldRelationTypeEntity>(criter).FirstOrDefault();

                if (entity != null)
                {
                    lstDbType[dbTypeName.ToLower()] = entity;
                }
            }
            if (lstDbType.ContainsKey(dbTypeName.ToLower()) && lstDbType[dbTypeName.ToLower()] != null)
            {
                DbFieldRelationTypeEntity entity = lstDbType[dbTypeName.ToLower()];
                if (entity != null)
                {
                    return entity.DbFieldRelationTypeId;
                }
            }
            return 1;

        }
        /// <summary>
        /// 获取数据源
        /// </summary>
        /// <param name="grid"></param>
        /// <param name="data"></param>
        /// <returns></returns>
        public virtual LigerGrid GetDbSource(LigerGrid grid, string id)
        {
            var entity = this.GetById<DbTableEntity>(id);
            if (entity == null)
            {
                return grid;
            }
            DbServerEntity dbServer = this.GetById<DbServerEntity>(entity.DbServerId.ToString());
            if (dbServer == null)
            {
                return grid;
            }
            List<DbColumnEntity> lstEntity = new List<DbColumnEntity>();
            var dbHelper = this.CreateRepository(dbServer.ProviderType, dbServer.ConntionString);
            return dbHelper.GetSource(entity.DbTableName, grid);


        }
        /// <summary>
        /// 验证导入表字段与表结构是否匹配
        /// </summary>
        /// <param name="dt">导入表</param>
        /// <param name="dbColumn">表结构字段</param>
        /// <param name="strError">错误信息</param>
        /// <returns></returns>
        public virtual bool CheckColumn(DataTable dt, List<DbColumnEntity> dbColumn, ref string strError)
        {

            var notnullCol = dbColumn.Where(x => !x.AllowDBNull && x.IsEnabled && !x.IsIdentifier && !SystemColumnNames.Contains(x.ColumnName.ToLower())).ToList();
            if (dt.Columns.Count < notnullCol.Count - 1)
            {
                strError = "该数据表与需要导入的表结构不匹配，无法导入";
                return false;
            }
            List<int> nullCol = new List<int>();
            int index = 0;
            var indexIdentifier = -1;
            #region 验证表字段是否正确
            foreach (DataColumn column in dt.Columns)
            {
                var obj = notnullCol.Where(x => x.ColumnName.ToLower().Trim().Equals(column.ColumnName.ToLower().Trim())
                 || (!string.IsNullOrWhiteSpace(x.ColumnCaption) && x.ColumnCaption.ToLower().Trim().Equals(column.ColumnName.ToLower().Trim()))).FirstOrDefault();
                if (obj != null)
                {
                    if (!obj.IsIdentifier)
                    {
                        column.ColumnName = obj.ColumnName;
                    }
                    else
                    {
                        indexIdentifier = index;
                    }

                }
                else
                {
                    nullCol.Add(index);
                }
                index++;
            }

            if (nullCol.Count > 0)
            {
                foreach (var i in nullCol)
                {
                    var obj = dbColumn.Where(x => x.AllowDBNull && (x.ColumnName.ToLower().Trim().Equals(dt.Columns[i].ColumnName.ToLower().Trim())
               || (!string.IsNullOrWhiteSpace(x.ColumnCaption) && x.ColumnCaption.ToLower().Trim().Equals(dt.Columns[i].ColumnName.ToLower().Trim())))).FirstOrDefault();
                    if (obj != null)
                    {
                        if (!obj.IsIdentifier)
                        {
                            dt.Columns[i].ColumnName = obj.ColumnName;
                        }

                    }
                    else
                    {

                    }
                }
            }
            if (indexIdentifier >= 0)
            {
                dt.Columns.RemoveAt(indexIdentifier);
            }
            #endregion

            return true;
        }

        /// <summary>
        /// 保存列属性
        /// </summary>
        /// <param name="id"></param>
        /// <param name="dbColumn"></param>
        /// <param name="strError"></param>
        /// <returns></returns>
        public bool SaveColumnsCell(string id, DbColumnEntity dbColumn, ref string strError)
        {
            if (string.IsNullOrWhiteSpace(id))
            {
                strError = "参数值不正确";
                return false;
            }
            dbColumn.ColumnId = id;
            var relationTypeService = AppHttpContext.GetSerivce<IDbFieldRelationTypeService>();
            var typeModel = relationTypeService.GetById(dbColumn.DbFieldRelationTypeId.ToString());
            if (typeModel == null)
            {
                strError = "类型实体未找到";
                return false;
            }
            dbColumn.SysFieldTypeId = typeModel.SysFieldTypeId;
            UpdateColumnDescription(dbColumn, ref strError);
            return this.Update<DbColumnEntity>(dbColumn);

        }

        /// <summary>
        /// 获取表结构树
        /// </summary>
        /// <param name="parentid"></param>
        /// <param name="dbServerType"></param>
        /// <param name="action"></param>
        /// <returns></returns>
        public List<DbTreeDto> GetDbTrees(string parentid,int dbServerType,string action)
        {
            List<DbTreeDto> dbTreeDtos = new List<DbTreeDto>();
            if (dbServerType == 0)
            {
                if (action.ToUpper().Equals("T"))
                {
                    DbServerEntity entity = new DbServerEntity
                    {
                        DbServerId = "0",
                        DbServerName = "DBManagerList",
                        LocalName = "服务器列表",
                        ProviderType = -1,
                        ConntionString = "",
                        children = true,
                        action = "DB",
                        parent="#"
                    };
                    dbTreeDtos = new List<DbServerEntity>() { entity }.MapTo<List<DbTreeDto>>();
                }
                else if (action.ToUpper().Equals("DBC"))
                {
                    dbTreeDtos.Add(new DbTreeDto
                    {
                        action = "T",
                        id = parentid + "_T",
                        parent = parentid + "_S",
                        SourceId = parentid,
                        icon = "fa fa-table",
                        children = true,
                        DbServerType = 1,
                        ProviderType = 1,
                        text = "表"
                    });
                    dbTreeDtos.Add(new DbTreeDto
                    {
                        action = "V",
                        id = parentid + "_V",
                        parent = parentid + "_S",
                        SourceId = parentid,
                        icon = "fa fa-table",
                        children = true,
                        DbServerType = 1,
                        ProviderType = 1,
                        text = "视图"
                    });
                    var parent= this.GetById<DbServerEntity>(parentid);
                    if (string.IsNullOrWhiteSpace(parent.ParentId))
                    {
                        dbTreeDtos.Add(new DbTreeDto
                        {
                            action = "DB",
                            id = parentid + "_0_S",
                            parent = parentid + "_S",
                            SourceId = parentid,
                            icon = "fa fa-database",
                            children = true,
                            DbServerType = 0,
                            ProviderType = -1,
                            text = "目标库"
                        });
                    }
                    
                }
                else
                {
                    var lstServer = AppHttpContext.GetSerivce<IDbServerService>().GetChildren(parentid);
                    if (lstServer != null && lstServer.Count > 0)
                    {
                        lstServer.ForEach(x =>
                        {
                            x.parent =(!string.IsNullOrWhiteSpace(x.ParentId)? x.ParentId+"_0":"0") + "_S";
                            x.action = "DBC";
                            x.children = true;
                        });
                        dbTreeDtos = lstServer.MapTo<List<DbTreeDto>>();
                    }
                }


            }
            else if (dbServerType == 1)
            {
                var lstTable = this.GetDbTable(parentid, action.ToUpper().Equals("T"));
                if (lstTable != null)
                {
                    dbTreeDtos = lstTable.MapTo<List<DbTreeDto>>();
                }
                
            }
            //else
            //{
      
            //    var lstItems = new object[] {
            //        new { text = "字段", id = parentid.ToString()+"_C" ,icon="fa fa-columns" ,parent=parentid+"_DT", SourceId = parentid.ToString(), DbServerType = 3, action = "1",children=false },
            //        new { text = "数据",id = parentid.ToString()+"_D",icon="fa fa-list-alt", SourceId = parentid.ToString(),parent=parentid+"_DT", DbServerType = 3, action = "2",children=false } };
             
            //}
            return dbTreeDtos;
        }

    }
}
